USE [DeliverySystem]


-- -- -- -- --  D E L E T E   E X I S T I N G   D A T A  -- -- -- -- -- 

-- Delete existing log
DELETE FROM [dbo].[log]

-- Delete existing users
DELETE FROM [dbo].[User]
-- Reset ID value to 1
DBCC CheckIdent ([User], RESEED, 1)


-- Delete existing Roles
DELETE FROM [dbo].[Role]
-- Reset ID value to 1
DBCC CheckIdent ([Role], RESEED, 1)


-- Delete existing ContentImage
DELETE FROM [dbo].[ContentImage]
-- Reset ID value to 1
DBCC CheckIdent ([ContentImage], RESEED, 1)


-- Delete existing ArticleContent
DELETE FROM [dbo].[ArticleContent]
-- Reset ID value to 1
DBCC CheckIdent ([ArticleContent], RESEED, 1)


-- Delete existing Article
DELETE FROM [dbo].[Article]
-- Reset ID value to 1
DBCC CheckIdent ([Article], RESEED, 1)


-- Delete existing publications
DELETE FROM [dbo].[Publication]
-- Reset ID value to 1
DBCC CheckIdent ([Publication], RESEED, 1)


-- Delete existing companys
DELETE FROM [dbo].[Company]
-- Reset ID value to 1
DBCC CheckIdent ([Company], RESEED, 1)

-- Delete existing language
DELETE FROM [dbo].[Language]
DBCC CheckIdent ([Language], RESEED, 1)

GO



--INSERT TEST DATA INTO THE [Company] TABLE
if not exists(select * from [Company] where CompanyName ='KLS')
INSERT INTO [dbo].[Company]
           ([CompanyName]           
           ,[CreatedDate]  )         
     VALUES ('KLS', (select GETDATE()) )
GO


--INSERT TEST DATA INTO THE [Language] TABLE
if not exists(select * from [Language] where Name ='Danish')
INSERT INTO [dbo].[Language]
           ([Name]
           ,[Code])
     VALUES
           ('Danish'
           ,'da-DK')
GO


if not exists(select * from [Language] where Name ='English')
INSERT INTO [dbo].[Language]
           ([Name]
           ,[Code])
     VALUES
           ('English'
           ,'en-GB')
GO

if not exists(select * from [Language] where Name ='Norwegian')
INSERT INTO [dbo].[Language]
           ([Name]
           ,[Code])
     VALUES
           ('Norwegian'
           ,'no')
GO

if not exists(select * from [Language] where Name ='Swedish')
INSERT INTO [dbo].[Language]
           ([Name]
           ,[Code])
     VALUES
           ('Swedish'
           ,'sv-SE')
GO


--INSERT TEST DATA INTO THE Role TABLE
if not exists(select * from [Role] where RoleName ='Superadmin')
INSERT INTO [dbo].[Role]
           ([CompanyId]
           ,[RoleName]          
           ,[CreatedDate]           
           )
     VALUES
           ((select top 1 [Company].CompanyId from [Company])
           ,'Superadmin'           
           ,(select GETDATE())           
           )
GO


if not exists(select * from [Role] where RoleName ='Editor')
INSERT INTO [dbo].[Role]
           ([CompanyId]
           ,[RoleName]		   
           ,[CreatedDate]           
           )
     VALUES
           ((select top 1 [Company].CompanyId from [Company])
           ,'Editor'		           
           ,(select GETDATE())           
           )
GO

if not exists(select * from [Role] where RoleName ='Graphic Designer')
INSERT INTO [dbo].[Role]
           ([CompanyId]
           ,[RoleName]           
           ,[CreatedDate]           
           )
     VALUES
           ((select top 1 [Company].CompanyId from [Company])
           ,'Graphic Designer'           
           ,(select GETDATE())           
           )
GO


if not exists(select * from [Role] where RoleName ='Journalist')
INSERT INTO [dbo].[Role]
           ([CompanyId]
           ,[RoleName]
           
           ,[CreatedDate]
           
           )
     VALUES
           ((select top 1 [Company].CompanyId from [Company])
           ,'Journalist'           
           ,(select GETDATE())           
           )
GO


--INSERT TEST DATA INTO THE [User] TABLE
if not exists(select * from [User] where UserName ='Admin')
INSERT INTO [dbo].[User]
           ([UserName]
           ,[Password]                     
           ,[RoleId]  
		   ,[LanguageId]         
           ,[CreatedDate]
           )
     VALUES
           ('Admin'
           ,'E2aejA'           
           ,(select  [Role].RoleId from [Role] where [Role].RoleName = 'Superadmin')
		    ,(select top 1 [Language].LanguageId from [Language])
           ,(select GETDATE())
           )
GO




